<?
class experticias_manual{

	// Propiedades
	var $id;
	var $id_perito;
	var $codigo_perito;
	var $nombre_perito;
	var $id_zona;
	var $codigo_zona;
	var $nombre_zona;
	var $observaciones;
	var $limites;
	var $apatv;
	var $fideicomiso;
	
	var $total;

	function get($conn, $id){
		try {
			$q = "SELECT A.*, B.id_zona, B.codigo AS codigo_perito, B.nombre AS nombre_perito, E.codigo AS codigo_zona, E.nombre AS nombre_zona
			      FROM  experticias_manuales AS A 
					LEFT JOIN peritos AS B ON A.id_perito = B.id 
					LEFT JOIN zonas AS E ON B.id_zona = E.id  
				  WHERE A.id=$id";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			if(!$r->EOF){
				$this->id = $r->fields['id'];
				$this->id_perito = $r->fields['id_perito'];
				$this->codigo_perito = $r->fields['codigo_perito'];
				$this->nombre_perito = $r->fields['nombre_perito'];
				$this->id_zona = $r->fields['id_zona'];
				$this->codigo_zona = $r->fields['codigo_zona'];
				$this->nombre_zona = $r->fields['nombre_zona'];
				$this->observaciones = $r->fields['observaciones'];
				$this->fecha = $r->fields['fecha'];
				$this->apatv = $r->fields['tasa_asociacion'];
				$this->fideicomiso = $r->fields['tasa_fideicomiso'];
			}


				$q = "SELECT A.*, COALESCE(id_material, id_tipo) AS id_tipo_material, B.nombre FROM experticias_manuales_detalle AS A
						INNER JOIN tipos_experticia AS B ON A.id_tipo = B.id
					WHERE id_experticias_manuales = $id";
				$rL = $conn->Execute(utf8_encode($q));
				$i = 0;
				while(!$rL->EOF){
					$LimitesAux[$i][0] = $rL->fields['id_tipo'];
					$LimitesAux[$i][1] = $rL->fields['nombre'];
					$LimitesAux[$i][2] = $rL->fields['id_tipo_material'];
					$LimitesAux[$i][3] = $rL->fields['cantidad'];
					$LimitesAux[$i][4] = $rL->fields['costo'];
					
					$i++;

					$rL->movenext();
				}
				$this->limites = new Services_JSON();
				$this->limites = (is_array($LimitesAux) ? $this->limites->encode($LimitesAux) : false);

		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function get_all($conn, $orden="id DESC", $Tipo, $Valor, $TipoE, $max=10, $from=1){
		try {

			$TipoE = empty($TipoE) ? 0 : $TipoE;
			
			if(empty($Valor)){
				$q = "SELECT id FROM experticias_manuales ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==0){
				$q = "SELECT A.id FROM  experticias_manuales AS A LEFT JOIN peritos AS B ON A.id_perito = B.id LEFT JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			//	die($q);			

			}
			elseif($Tipo==1){
				$q = "SELECT A.id FROM  experticias_manuales AS A LEFT JOIN peritos AS B ON A.id_perito = B.id LEFT JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= "WHERE B.nombre::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==2){
				$q = "SELECT A.id FROM  experticias_manuales AS A LEFT JOIN peritos AS B ON A.id_perito = B.id LEFT JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= "WHERE A.fecha::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==3){
				$q = "SELECT A.id FROM  experticias_manuales AS A LEFT JOIN peritos AS B ON A.id_perito = B.id LEFT JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= "WHERE C.codigo::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			//	die($q);			

			}
			elseif($Tipo==4){
				$q = "SELECT A.id FROM  experticias_manuales AS A LEFT JOIN peritos AS B ON A.id_perito = B.id LEFT JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= "WHERE B.codigo::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}

			//die($q);			
			$r = ($max!=0) ? $conn->SelectLimit($q, $max, $from) : $conn->Execute(utf8_encode($q));
			$coleccion=array();
			while(!$r->EOF){
				$ue = new experticias_manual;
				$ue->get($conn, $r->fields['id']);
				$coleccion[] = $ue;
				$r->movenext();
			}
			$this->total = $r->RecordCount();
			return $coleccion;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function add($conn, $id_perito, $observaciones, $fecha, $limites, $apatv, $fideicomiso){
		try {
			/*
			$q = "INSERT INTO experticias_manuales ";
			$q.= "(id_perito, observaciones, fecha) ";
			$q.= "VALUES ";
			$q.= "(NULLIF($id_perito,-1), upper('$observaciones'), NULLIF('$fecha','')::date ) RETURNING id";
			*/

			$q = "INSERT INTO experticias_manuales ";
			$q.= "(id_perito, observaciones, fecha, tasa_asociacion, tasa_fideicomiso) ";
			$q.= "VALUES ";
			$q.= "(NULLIF($id_perito,-1), upper('$observaciones'), NULLIF('$fecha','')::date, $apatv, $fideicomiso )";
			
			//die($q);
			$r = $conn->Execute(utf8_encode($q));

			$q2 = "SELECT MAX(id) AS id FROM experticias_manuales";
			$r2 = $conn->Execute(utf8_encode($q2));

			$this->auditoria($conn, $q);
			
			/*
			$this->GuardarLimites($conn, $r->fields['id'], $limites);
			*/

			$this->GuardarLimites($conn, $r2->fields['id'], $limites);

			return REG_ADD_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function set($conn, $id, $id_perito, $observaciones, $fecha, $limites, $apatv, $fideicomiso){
		try {
			$q = "UPDATE experticias_manuales SET id_perito= NULLIF($id_perito,-1), observaciones = upper('$observaciones'), fecha = NULLIF('$fecha','')::date, tasa_asociacion = $apatv, tasa_fideicomiso = $fideicomiso ";
			$q.= " WHERE id=$id";	
			
			//die($q);
			$conn->Execute(utf8_encode($q));

			$this->auditoria($conn, $q);
			
			$this->GuardarLimites($conn, $id, $limites);
			
			return REG_SET_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function GuardarLimites($conn,$id,$limites){
		try {
			$JsonRec = new Services_JSON();
			$JsonRec=$JsonRec->decode(str_replace("\\","",$limites));
			$q = "DELETE FROM experticias_manuales_detalle WHERE id_experticias_manuales=$id";
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			if(is_array($JsonRec->limites)){
				foreach($JsonRec->limites as $limitesAux){
					$cantidad = guardafloat($limitesAux[3]);
					$costo = guardafloat($limitesAux[4]);
					
					$q = "INSERT INTO experticias_manuales_detalle ";
					$q.= "(id_experticias_manuales, id_tipo, cantidad, costo, id_material) ";
					$q.= "VALUES ";
					$q.= "($id, $limitesAux[0], $cantidad, $costo, $limitesAux[2]) ";
					//die($q);
					$conn->Execute(utf8_encode($q));
					
					$this->auditoria($conn, $q);
				}
			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function del($conn, $id){
		try {
			$q = "DELETE FROM experticias_manuales WHERE id=$id";
			//die($q);
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			return REG_DEL_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function total_registro_busqueda($conn,$Tipo, $Valor, $TipoE, $orden="id")
	{
		$TipoE = empty($TipoE) ? 0 : $TipoE;
		if(empty($Valor)){
			$q = "SELECT count(*) AS total FROM experticias_manuales";
		}
		elseif($Tipo==0){
			$q = "SELECT count(*) AS total FROM  experticias_manuales AS A LEFT JOIN peritos AS B ON A.id_perito = B.id LEFT JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= " WHERE C.nombre::text ILIKE '%$Valor%'::text ";
		}
		elseif($Tipo==1){
			$q = "SELECT count(*) AS total FROM  experticias_manuales AS A LEFT JOIN peritos AS B ON A.id_perito = B.id LEFT JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= " WHERE B.nombre::text ILIKE '%$Valor%'::text ";
		}
		elseif($Tipo==2){
			$q = "SELECT count(*) AS total FROM  experticias_manuales AS A LEFT JOIN peritos AS B ON A.id_perito = B.id LEFT JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= " WHERE A.fecha::text ILIKE '%$Valor%'::text ";
		}
		elseif($Tipo==3){
			$q = "SELECT count(*) AS total FROM  experticias_manuales AS A LEFT JOIN peritos AS B ON A.id_perito = B.id LEFT JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= " WHERE C.codigo::text ILIKE '%$Valor%'::text ";
		}
		elseif($Tipo==4){
			$q = "SELECT count(*) AS total FROM  experticias_manuales AS A LEFT JOIN peritos AS B ON A.id_perito = B.id LEFT JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= " WHERE B.codigo::text ILIKE '%$Valor%'::text ";
		}
		

		$r = $conn->Execute(utf8_encode($q));
		$total = $r->fields['total'];
		return $total;
	}
	
	function auditoria($conn, $query) {
		$id_usuario = $_SESSION['id'];
		$q = "INSERT INTO auditoria(id_usuario, query) VALUES($id_usuario,$$" . $query . "$$);";
		//die($q);
		$conn->Execute(utf8_encode($q));

		return true;
	}	
	
}
?>
